{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This notebook assumes to be running from your FireCARES VM (eg. python manage.py shell_plus --notebook --no-browser)\n",
    "\n",
    "import sys\n",
    "import os\n",
    "import time\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "sys.path.insert(0, os.path.realpath('..'))\n",
    "import folium\n",
    "import django\n",
    "import sqlite3\n",
    "django.setup()\n",
    "from django.db import connections\n",
    "from pretty import pprint\n",
    "from firecares.firestation.models import (FireDepartment, FireStation, NFIRSStatistic, FireDepartmentRiskModels,\n",
    "                                          PopulationClassQuartile)\n",
    "from fire_risk.models import DIST, DISTMediumHazard, DISTHighHazard\n",
    "from fire_risk.models.DIST.providers.ahs import ahs_building_areas\n",
    "from fire_risk.models.DIST.providers.iaff import response_time_distributions\n",
    "from django.db.models import Avg, Max, Min, Q\n",
    "from django.contrib.gis.geos import GEOSGeometry\n",
    "from IPython.display import display\n",
    "from firecares.utils import lenient_summation, dictfetchall\n",
    "from firecares.tasks.update import (calculate_department_census_geom, calculate_story_distribution,\n",
    "                                    calculate_structure_counts, update_performance_score, update_nfirs_counts,\n",
    "                                    dist_model_for_hazard_level)\n",
    "pd.set_option(\"display.max_rows\", 2000)\n",
    "pd.set_option(\"display.max_columns\", 100)\n",
    "\n",
    "def display_geom(geom):\n",
    "    _map = folium.Map(location=[geom.centroid.y, geom.centroid.x],\n",
    "                      tiles='Stamen Toner')\n",
    "    _map.choropleth(geo_str=geom.geojson, line_weight=0, fill_opacity=0.2, fill_color='green')\n",
    "    ll = geom.extent[1::-1]\n",
    "    ur = geom.extent[3:1:-1]\n",
    "    _map.fit_bounds([ll, ur])\n",
    "\n",
    "    return _map\n",
    "\n",
    "# Philadephia-specific\n",
    "fd = FireDepartment.objects.get(id=91907)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(pd.read_sql_query('select region, count(1) from firestation_firedepartment group by region', connections['default']))\n",
    "\n",
    "# FDs w/ NO region\n",
    "display(pd.read_sql_query('select id, name, state from firestation_firedepartment where region IS NULL', connections['default']))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Fire Risk = risk_model_fires_quartile\n",
    "- Fire Spread Risk = risk_model_size1_percent_size2_percent_sum_quartile\n",
    "- Death and Injury Risk = risk_model_deaths_injuries_sum_quartile\n",
    "\n",
    "All of the following are pulled from the predictions model per department, by structure hazard level:\n",
    "\n",
    "- rm.risk_model_deaths,\n",
    "- rm.risk_model_injuries,\n",
    "- rm.risk_model_fires,\n",
    "- rm.risk_model_fires_size0,\n",
    "- rm.risk_model_fires_size0_percentage,\n",
    "- rm.risk_model_fires_size1,\n",
    "- rm.risk_model_fires_size1_percentage,\n",
    "- rm.risk_model_fires_size2,\n",
    "- rm.risk_model_fires_size2_percentage,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "quartiles = \"\"\"\n",
    "SELECT\n",
    "    (SELECT COALESCE(rm.risk_model_fires_size1_percentage,0)+COALESCE(rm.risk_model_fires_size2_percentage,0)) AS \"risk_model_size1_percent_size2_percent_sum\",\n",
    "\n",
    "    (SELECT COALESCE(rm.risk_model_deaths,0)+COALESCE(rm.risk_model_injuries,0)) AS \"risk_model_deaths_injuries_sum\",\n",
    "       fd.\"id\",\n",
    "       fd.\"created\",\n",
    "       fd.\"modified\",\n",
    "       fd.\"fdid\",\n",
    "       fd.\"name\",\n",
    "       fd.\"headquarters_address_id\",\n",
    "       fd.\"mail_address_id\",\n",
    "       fd.\"headquarters_phone\",\n",
    "       fd.\"headquarters_fax\",\n",
    "       fd.\"department_type\",\n",
    "       fd.\"organization_type\",\n",
    "       fd.\"website\",\n",
    "       fd.\"state\",\n",
    "       fd.\"region\",\n",
    "       rm.\"dist_model_score\",\n",
    "       rm.\"risk_model_deaths\",\n",
    "       rm.\"risk_model_injuries\",\n",
    "       rm.\"risk_model_fires\",\n",
    "       rm.\"risk_model_fires_size0\",\n",
    "       rm.\"risk_model_fires_size0_percentage\",\n",
    "       rm.\"risk_model_fires_size1\",\n",
    "       rm.\"risk_model_fires_size1_percentage\",\n",
    "       rm.\"risk_model_fires_size2\",\n",
    "       rm.\"risk_model_fires_size2_percentage\",\n",
    "       fd.\"population\",\n",
    "       fd.\"population_class\",\n",
    "       fd.\"featured\",\n",
    "       nfirs.avg_fires AS \"residential_fires_avg_3_years\",\n",
    "       rm.\"level\",\n",
    "       CASE\n",
    "           WHEN (rm.\"risk_model_fires_size1_percentage\" IS NOT NULL\n",
    "                 OR rm.\"risk_model_fires_size2_percentage\" IS NOT NULL) THEN ntile(4) over (partition BY COALESCE(rm.risk_model_fires_size1_percentage,0)+COALESCE(rm.risk_model_fires_size2_percentage,0) != 0, fd.population_class, rm.level\n",
    "                                                                                            ORDER BY COALESCE(rm.risk_model_fires_size1_percentage,0)+COALESCE(rm.risk_model_fires_size2_percentage,0))\n",
    "           ELSE NULL\n",
    "       END AS \"risk_model_size1_percent_size2_percent_sum_quartile\",\n",
    "       CASE\n",
    "           WHEN (rm.\"risk_model_deaths\" IS NOT NULL\n",
    "                 OR rm.\"risk_model_injuries\" IS NOT NULL) THEN ntile(4) over (partition BY COALESCE(rm.risk_model_deaths,0)+COALESCE(rm.risk_model_injuries,0) != 0, fd.population_class, rm.level\n",
    "                                                                              ORDER BY COALESCE(rm.risk_model_deaths,0)+COALESCE(rm.risk_model_injuries,0))\n",
    "           ELSE NULL\n",
    "       END AS \"risk_model_deaths_injuries_sum_quartile\",\n",
    "       CASE\n",
    "           WHEN rm.\"dist_model_score\" IS NOT NULL THEN ntile(4) over (partition BY rm.dist_model_score IS NOT NULL, fd.population_class, rm.level\n",
    "                                                                      ORDER BY rm.dist_model_score)\n",
    "           ELSE NULL\n",
    "       END AS \"dist_model_score_quartile\",\n",
    "       CASE\n",
    "           WHEN rm.\"risk_model_deaths\" IS NOT NULL THEN ntile(4) over (partition BY rm.risk_model_deaths IS NOT NULL, fd.population_class, rm.level\n",
    "                                                                       ORDER BY rm.risk_model_deaths)\n",
    "           ELSE NULL\n",
    "       END AS \"risk_model_deaths_quartile\",\n",
    "       CASE\n",
    "           WHEN rm.\"risk_model_injuries\" IS NOT NULL THEN ntile(4) over (partition BY rm.risk_model_injuries IS NOT NULL, fd.population_class, rm.level\n",
    "                                                                         ORDER BY rm.risk_model_injuries)\n",
    "           ELSE NULL\n",
    "       END AS \"risk_model_injuries_quartile\",\n",
    "       CASE\n",
    "           WHEN rm.\"risk_model_fires_size0\" IS NOT NULL THEN ntile(4) over (partition BY rm.risk_model_fires_size0 IS NOT NULL, fd.population_class, rm.level\n",
    "                                                                            ORDER BY rm.risk_model_fires_size0)\n",
    "           ELSE NULL\n",
    "       END AS \"risk_model_fires_size0_quartile\",\n",
    "       CASE\n",
    "           WHEN rm.\"risk_model_fires_size1\" IS NOT NULL THEN ntile(4) over (partition BY rm.risk_model_fires_size1 IS NOT NULL, fd.population_class, rm.level\n",
    "                                                                            ORDER BY rm.risk_model_fires_size1)\n",
    "           ELSE NULL\n",
    "       END AS \"risk_model_fires_size1_quartile\",\n",
    "       CASE\n",
    "           WHEN rm.\"risk_model_fires_size2\" IS NOT NULL THEN ntile(4) over (partition BY rm.risk_model_fires_size2 IS NOT NULL, fd.population_class, rm.level\n",
    "                                                                            ORDER BY rm.risk_model_fires_size2)\n",
    "           ELSE NULL\n",
    "       END AS \"risk_model_fires_size2_quartile\",\n",
    "       CASE\n",
    "           WHEN rm.\"risk_model_fires\" IS NOT NULL THEN ntile(4) over (partition BY rm.risk_model_fires IS NOT NULL, fd.population_class, rm.level\n",
    "                                                                      ORDER BY rm.risk_model_fires)\n",
    "           ELSE NULL\n",
    "       END AS \"risk_model_fires_quartile\",\n",
    "       CASE\n",
    "           WHEN \"nfirs\".\"avg_fires\" IS NOT NULL THEN ntile(4) over (partition BY avg_fires IS NOT NULL, fd.population_class, rm.level\n",
    "                                                                    ORDER BY avg_fires)\n",
    "           ELSE NULL\n",
    "       END AS \"residential_fires_avg_3_years_quartile\"\n",
    "FROM \"firestation_firedepartment\" fd\n",
    "INNER JOIN \"firestation_firedepartmentriskmodels\" rm ON rm.department_id = fd.id\n",
    "LEFT JOIN\n",
    "    ( SELECT fire_department_id,\n",
    "             AVG(COUNT) AS avg_fires,\n",
    "                           LEVEL\n",
    "     FROM firestation_nfirsstatistic\n",
    "     WHERE YEAR >= 2010\n",
    "         AND metric='residential_structure_fires'\n",
    "     GROUP BY fire_department_id,\n",
    "              LEVEL) nfirs ON (fd.id=nfirs.fire_department_id\n",
    "                               AND nfirs.LEVEL = rm.LEVEL)\n",
    "WHERE archived=FALSE\n",
    "ORDER BY id\n",
    "\"\"\"\n",
    "\n",
    "df = pd.read_sql_query(quartiles, connections['default'])\n",
    "\n",
    "levels = {'0': 'All hazard levels', '1': 'Low hazard', '2': 'Medium hazard', '4': 'High hazard', '5': 'Unknown hazard'}\n",
    "quartiles = {'1': 'Low risk', '2': 'Medium risk', '3': 'Medium risk', '4': 'High risk'}\n",
    "\n",
    "# Transformations to human-readable values\n",
    "df['level'] = df['level'].apply(lambda x: levels[str(x)])\n",
    "for c in ['risk_model_size1_percent_size2_percent_sum_quartile', 'risk_model_deaths_injuries_sum_quartile', 'dist_model_score_quartile', 'risk_model_deaths_quartile', 'risk_model_injuries_quartile', 'risk_model_fires_size0_quartile', 'risk_model_fires_size1_quartile', 'risk_model_fires_size2_quartile', 'risk_model_fires_quartile', 'residential_fires_avg_3_years_quartile']:\n",
    "    df[c] = df[c].apply(lambda x: quartiles[str(int(x))] if not np.isnan(x) else 'N/A')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "quartiles = \"\"\"\n",
    "SELECT\n",
    "    fd.id,\n",
    "    fd.name,\n",
    "        CASE WHEN rm.dist_model_score IS NOT NULL\n",
    "        THEN ntile(4) over (partition by rm.dist_model_score is not null, fd.population_class, rm.level order by rm.dist_model_score)\n",
    "        ELSE NULL\n",
    "        END\n",
    "    AS dist_model_score_quartile, \n",
    "    rm.dist_model_score,\n",
    "    rm.level,\n",
    "    fd.population_class,\n",
    "    nfirs.avg_fires as residential_fires_avg_3_years\n",
    "\n",
    "FROM firestation_firedepartment fd\n",
    "INNER JOIN firestation_firedepartmentriskmodels rm ON\n",
    "    rm.department_id = fd.id\n",
    "LEFT JOIN (\n",
    "    SELECT fire_department_id, AVG(count) as avg_fires, level\n",
    "    FROM firestation_nfirsstatistic\n",
    "    WHERE year >= 2010 AND metric = 'residential_structure_fires'\n",
    "    GROUP BY fire_department_id, level) AS nfirs\n",
    "ON (fd.id = nfirs.fire_department_id and nfirs.level = rm.level)\n",
    "WHERE archived = False and dist_model_score is not null\n",
    "AND population_class = 9\n",
    "ORDER BY id\n",
    "LIMIT 2000\n",
    "\"\"\"\n",
    "\n",
    "df = pd.read_sql_query(quartiles, connections['default'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "quartiles = \"\"\"\n",
    "SELECT\n",
    "    fd.id,\n",
    "    fd.name,\n",
    "        CASE WHEN nfirs.avg_fires IS NOT NULL\n",
    "        THEN ntile(4) over (partition by avg_fires is not null, fd.population_class, rm.level order by avg_fires)\n",
    "        ELSE NULL\n",
    "        END\n",
    "    AS residential_fires_avg_3_years_quartile,\n",
    "    rm.dist_model_score,\n",
    "    rm.level,\n",
    "    fd.population_class,\n",
    "    nfirs.avg_fires as residential_fires_avg_3_years\n",
    "    mfirs.\n",
    "\n",
    "FROM firestation_firedepartment fd\n",
    "INNER JOIN firestation_firedepartmentriskmodels rm ON\n",
    "    rm.department_id = fd.id\n",
    "LEFT JOIN (\n",
    "    SELECT fire_department_id, AVG(count) as avg_fires, level\n",
    "    FROM firestation_nfirsstatistic\n",
    "    WHERE year >= 2010 AND metric = 'residential_structure_fires'\n",
    "    GROUP BY fire_department_id, level) AS nfirs\n",
    "ON (fd.id = nfirs.fire_department_id and nfirs.level = rm.level)\n",
    "WHERE archived = False and dist_model_score is not null\n",
    "AND population_class = 9\n",
    "ORDER BY id\n",
    "LIMIT 2000\n",
    "\"\"\"\n",
    "\n",
    "df = pd.read_sql_query(quartiles, connections['default'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "quartiles = \"\"\"\n",
    "SELECT\n",
    "    fd.id,\n",
    "    fd.name,\n",
    "        CASE WHEN nfirs.avg_fires IS NOT NULL\n",
    "        THEN ntile(4) over (partition by avg_fires is not null, fd.population_class, rm.level order by avg_fires)\n",
    "        ELSE NULL\n",
    "        END\n",
    "    AS residential_fires_avg_3_years_quartile,\n",
    "        CASE WHEN rm.dist_model_score IS NOT NULL\n",
    "        THEN ntile(4) over (partition by rm.dist_model_score is not null, fd.population_class, rm.level order by rm.dist_model_score)\n",
    "        ELSE NULL\n",
    "        END\n",
    "    AS dist_model_score_quartile,\n",
    "    rm.dist_model_score,\n",
    "    rm.level,\n",
    "    fd.population_class,\n",
    "    nfirs.avg_fires as residential_fires_avg_3_years\n",
    "\n",
    "FROM firestation_firedepartment fd\n",
    "INNER JOIN firestation_firedepartmentriskmodels rm ON\n",
    "    rm.department_id = fd.id\n",
    "LEFT JOIN (\n",
    "    SELECT fire_department_id, AVG(count) as avg_fires, level\n",
    "    FROM firestation_nfirsstatistic\n",
    "    WHERE year >= 2010 AND metric = 'residential_structure_fires'\n",
    "    GROUP BY fire_department_id, level) AS nfirs\n",
    "ON (fd.id = nfirs.fire_department_id and nfirs.level = rm.level)\n",
    "WHERE archived = False and dist_model_score is not null\n",
    "AND population_class = 9\n",
    "ORDER BY id\n",
    "LIMIT 2000\n",
    "\"\"\"\n",
    "\n",
    "df = pd.read_sql_query(quartiles, connections['default'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q = \"\"\"SELECT fire_department_id, AVG(count) as avg_fires, SUM(count) as total_fires, level, SUM(count) > 75 as will_have_dist_score\n",
    "    FROM firestation_nfirsstatistic\n",
    "    WHERE year >= 2010 AND metric = 'residential_structure_fires' and fire_department_id = %(fd_id)s\n",
    "    GROUP BY fire_department_id, level\"\"\"\n",
    "\n",
    "pd.read_sql_query(q, connections['default'], params={'fd_id': 73343})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q = \"\"\"SELECT fd.id, nfirs.avg_fires, rm.level, rm.dist_model_score\n",
    "FROM firestation_firedepartment fd\n",
    "INNER JOIN firestation_firedepartmentriskmodels rm ON\n",
    "    rm.department_id = fd.id\n",
    "LEFT JOIN (\n",
    "    SELECT fire_department_id, AVG(count) as avg_fires, level\n",
    "    FROM firestation_nfirsstatistic\n",
    "    WHERE year >= 2010 AND metric = 'residential_structure_fires'\n",
    "    GROUP BY fire_department_id, level) AS nfirs\n",
    "ON (fd.id = nfirs.fire_department_id and nfirs.level = rm.level)\n",
    "WHERE fd.id = %(fd_id)s\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql_query(q, connections['default'], params={'fd_id': 73343})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Community Assessment\n",
    "\n",
    "### Risk Model\n",
    "\n",
    "All community assessment values are based on *predicted* metrics based on a NIST-developed risk model, current department coverage with _some_ data from the risk model is subset of the total number of fire departments.  The risk model does not necessarily include values that would be required to accurately calculate ALL 3 assessments on each department.\n",
    "\n",
    "### Fire Risk\n",
    "\n",
    "The community assessment in regards to fire risk for a specific department calculates the relative risk to similar departments in the same population class for the given structure hazard level.  This relative risk uses the predicted # of fires per year as the benchmark for risk, which is then ordered from least to greatest number of fires for the department and evenly split into 4 groups.  Departments that fall into the 1st group (lowest # of fires) are considered \"Low\" risk when compared to their peers in the same structure hazard level, those that fall into the 2nd and 3rd groups are considered \"Medium\" risk and those that fall into the last group (those with the greatest number of fires) are considered \"High\" risk.\n",
    "\n",
    "### Fire Spread Risk\n",
    "\n",
    "Similar to the fire risk assessment, the community assessment for fire _spread_ risk ONLY takes into account the predicted percentage of fires that extend beyond room of origin (the sum of size2 and size3 fire percentages relative to all fires for a department).  The predicted percentage of fires that spread beyond room of origin is then ordered in the same manner as in the fire risk assessment and divided into 4 each groups ranked from least to greatest percentage of fires that spread beyond room of origin, using the same risk designation, \"Low\", \"Medium\" and \"High\", assigned to the first, second/third and fourth groups, respectively.\n",
    "\n",
    "### Death and Injury Risk\n",
    "\n",
    "Death and injury risk takes into account the predicted # deaths and injuries caused during or response to fires per year [I *BELIEVE* THIS IS ALL DEATHS/INJURIES, INCLUDING FIREFIGHTERS AND CIVILIANS] for a department.  Similarly to the other 2 community assement metrics, the death and injury risk is a relative risk to other departments in the same population class and in response to structures of the same hazard level."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## TODO: Create mechanism to validate programatically"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
